# parsers/parser_001.py
from datetime import datetime
import re
import logging
import pandas as pd

# 兼容性：尝试导入 BaseParser、normalizer；若项目中已有会自动使用
try:
    from parsers.base_parser import BaseParser
except Exception:
    class BaseParser:
        pass

from core.normalizer import normalize_account_info, normalize_transaction_flow

logger = logging.getLogger(__name__)


class Parser001(BaseParser):
    """
    改进版工商银行解析器（parser_001）。
    特点：
      - 支持多个 sheet（sheet 名一般为开户人）
      - 支持 sheet 内存在多个开户行记录（账号），以及多段流水表（同一表格内重复表头）
      - 对常见表头同义词进行识别（如 工作日期/交易日期 / 交易时间戳 等）
      - 将解析结果返回 normalize 后的 DataFrame： (account_df, transaction_df)
    """
    BANK_NAME = "中国工商银行"

    def _clean(self, x):
        if pd.isna(x):
            return ''
        s = str(x)
        # 去掉常见控制字符和左右空白
        return s.replace('\t', '').strip()

    def _read_sheets_raw(self, file_path):
        # 不假设 header，全部按原始表格读入，便于逐行逐列扫描
        xls = pd.read_excel(file_path, sheet_name=None, header=None, dtype=str)
        for k, df in xls.items():
            xls[k] = df.fillna('').map(self._clean)
        return xls

    def _row_contains_any(self, row, keywords):
        text = " ".join([str(x) for x in row.values if x != ''])
        # 对于流水表头，需要更精确的匹配，避免误匹配开户信息行
        # 流水表头通常包含多个关键词，且格式更规范
        if keywords == ['借贷标志', '发生额', '余额']:
            # 必须包含至少2个流水关键词
            match_count = sum(1 for k in keywords if k in text)
            return match_count >= 2
        else:
            # 其他情况保持原有逻辑
            return any(k in text for k in keywords)

    def _find_header_rows(self, df, keywords):
        idxs = []
        for i, row in df.iterrows():
            if self._row_contains_any(row, keywords):
                idxs.append(i)
        return idxs

    def _parse_account_block(self, df, header_idx):
        """
        解析开户信息区：
          - header_idx 指向类似 '查询ID 姓名 证件类型 证件号码 客户编号 账号 ...' 的行
          - read subsequent rows until hitting a likely transaction-header 或者文件末尾
        返回：list of dict (每个 dict 对应一条开户记录)
        """
        header = df.iloc[header_idx].tolist()
        def find_col(names):
            for i, v in enumerate(header):
                for n in names:
                    if n == str(v):
                        return i
            return None

        # 查找各种字段的列位置
        col_query_id = find_col(['查询ID'])
        col_name = find_col(['姓名', '开户名', '户名'])
        col_id_type = find_col(['证件类型'])
        col_id = find_col(['证件号码', '证件号', '身份证'])
        col_customer_no = find_col(['客户编号'])
        col_account_type = find_col(['账号类型'])
        col_account = find_col(['账号', '帐号'])
        col_currency = find_col(['币种'])
        col_sub_account_seq = find_col(['子账户序号'])
        col_account_status = find_col(['账户状态'])
        col_open_date = find_col(['开户日期'])
        col_open_branch = find_col(['开户网点'])
        col_balance = find_col(['账户余额'])
        col_card_no = find_col(['账号对应卡号'])
        col_card_status = find_col(['卡片状态'])
        col_balance_date = find_col(['余额日期'])
        col_close_date = find_col(['销户日期'])
        col_close_institution = find_col(['销户机构'])
        col_balance_before_close = find_col(['销户前余额'])
        
        # 解析从 header_idx+1 开始
        accounts = []
        i = header_idx + 1
        while i < len(df):
            row = df.iloc[i]
            text = " ".join([str(x) for x in row.values if x != ''])
            # 遇到可能的流水表头（含借贷标志/发生额/余额）时停止账户区解析
            if '借贷标志' in text and '发生额' in text:
                break
            # 遇到空行或提示语时停止
            if text == '' or text.startswith('温馨提示'):
                i += 1
                break

            # 提取各种字段值
            query_id = row[col_query_id] if col_query_id is not None and row[col_query_id] != '' else None
            name = row[col_name] if col_name is not None and row[col_name] != '' else None
            id_type = row[col_id_type] if col_id_type is not None and row[col_id_type] != '' else None
            idno = row[col_id] if col_id is not None and row[col_id] != '' else None
            customer_no = row[col_customer_no] if col_customer_no is not None and row[col_customer_no] != '' else None
            account_type = row[col_account_type] if col_account_type is not None and row[col_account_type] != '' else None
            account_no = row[col_account] if col_account is not None and row[col_account] != '' else None
            currency = row[col_currency] if col_currency is not None and row[col_currency] != '' else None
            sub_account_seq = row[col_sub_account_seq] if col_sub_account_seq is not None and row[col_sub_account_seq] != '' else None
            account_status = row[col_account_status] if col_account_status is not None and row[col_account_status] != '' else None
            open_date = row[col_open_date] if col_open_date is not None and row[col_open_date] != '' else None
            open_branch = row[col_open_branch] if col_open_branch is not None and row[col_open_branch] != '' else None
            balance = row[col_balance] if col_balance is not None and row[col_balance] != '' else None
            card_no = row[col_card_no] if col_card_no is not None and row[col_card_no] != '' else None
            card_status = row[col_card_status] if col_card_status is not None and row[col_card_status] != '' else None
            balance_date = row[col_balance_date] if col_balance_date is not None and row[col_balance_date] != '' else None
            close_date = row[col_close_date] if col_close_date is not None and row[col_close_date] != '' else None
            close_institution = row[col_close_institution] if col_close_institution is not None and row[col_close_institution] != '' else None
            balance_before_close = row[col_balance_before_close] if col_balance_before_close is not None and row[col_balance_before_close] != '' else None

            # 某些导出会把 "姓名+证件号"连在一列，例如 "张三4102111968..."，尝试拆分
            if (not name) and (not idno):
                first = row.iloc[0]
                if isinstance(first, str) and re.search(r'\d{6,}', first):
                    m = re.match(r'([^\d]+)(\d{6,})', first)
                    if m:
                        name = m.group(1).strip()
                        idno = m.group(2).strip()

            accounts.append({
                '账号': account_no or None,
                '卡号': card_no or None,
                '开户名': name or None,
                '开户行名称': self.BANK_NAME,
                '证件号码': idno or None,
                '状态': account_status or None,
                '余额': balance or None,
                '销户日期': close_date or None,
                '销户前余额': balance_before_close or None,
                '手机号': None,  # 工商银行数据中通常没有手机号
                # 额外字段，用于存储更多信息
                '查询ID': query_id,
                '证件类型': id_type,
                '客户编号': customer_no,
                '账号类型': account_type,
                '币种': currency,
                '子账户序号': sub_account_seq,
                '开户日期': open_date,
                '开户网点': open_branch,
                '账号对应卡': card_no,
                '卡片状态': card_status,
                '余额日期': balance_date,
                '销户机构': close_institution
            })
            i += 1

        return accounts

    def _parse_transaction_blocks(self, df, sheet_name):
        """
        找到所有可能的流水表头（含 '借贷标志' 和 '发生额'等），然后解析后续若干行直到表尾或下一个表头
        返回：list of transaction dict
        每条 dict 包含：'账号','日期','摘要/用途','收支方向','金额','余额','对方账户信息'
        """
        tx_keywords = ['借贷标志', '发生额', '余额']
        header_idxs = self._find_header_rows(df, tx_keywords)
        transactions = []

        for h in header_idxs:
            header = df.iloc[h].tolist()

            def find_col(keys):
                for i, v in enumerate(header):
                    for k in keys:
                        if k == str(v):
                            return i
                return None

            # 查找各种字段的列位置
            col_account = find_col(['卡号', '帐号'])
            col_time = find_col(['交易时间戳'])
            col_transaction_no = find_col(['事件编号'])
            col_direction = find_col(['借贷标志'])
            col_amount = find_col(['发生额'])
            col_balance = find_col(['余额'])
            col_note = find_col(['注释'])
            col_currency = find_col(['币种'])
            col_counterparty_name = find_col(['对方户名'])
            col_counterparty_account = find_col(['对方帐户'])
            col_counterparty_bank = find_col(['对方开户行名'])
            col_counterparty_id = find_col(['对方证件号', '对方证件号码'])
            col_channel = find_col(['交易渠道', '渠道', '交易方式'])
            col_branch = find_col(['交易场所简称'])
            col_remark = find_col(['备注', '附言', '说明'])
            col_purpose_code = find_col(['交易代码'])
            col_status = find_col(['零售存取款摘要'])

            i = h + 1
            blank_streak = 0
            while i < len(df):
                row = df.iloc[i]
                row_text = " ".join([str(x) for x in row.values if x != ''])
                # 如果遇到另一表头则退出当前 block
                if self._row_contains_any(row, tx_keywords):
                    break
                if row_text == '':
                    blank_streak += 1
                    if blank_streak >= 3:  # 连续空行够多则认为本表结束
                        break
                    i += 1
                    continue
                blank_streak = 0

                # 取值（存在列索引则取，否则为 None）
                acc = row[col_account] if col_account is not None and row[col_account] != '' else None
                # 开户名 sheet 名一般为开户人姓名，需要先获取当前excel的sheet名
                acc_name = sheet_name


                # 时间：支持两种格式，同时保留时间戳对象和格式化的字符串
                date_time = row[col_time] if col_time is not None and row[col_time] != '' else None
                date_time_obj = None  # datetime对象
                date_time_str = None  # 格式化的字符串

                if date_time:
                    try:
                        date_str = str(date_time).strip()
                        
                        if ' ' in date_str and ':' in date_str:
                            # 格式1: 2008 08 22 09:39:43:648797
                            try:
                                date_time_obj = datetime.strptime(date_str, '%Y %m %d %H:%M:%S:%f')
                            except ValueError:
                                # 可能没有微秒
                                date_time_obj = datetime.strptime(date_str, '%Y %m %d %H:%M:%S')
                        else:
                            # 格式2: 2014-09-21-01.45.48.857380
                            date_str = date_str.replace('-', ' ').replace('.', ':')
                            try:
                                date_time_obj = datetime.strptime(date_str, '%Y %m %d %H:%M:%S:%f')
                            except ValueError:
                                # 可能没有微秒
                                date_time_obj = datetime.strptime(date_str, '%Y %m %d %H:%M:%S')
                        
                        # 如果成功解析，生成标准格式的时间字符串
                        if date_time_obj:
                            date_time_str = date_time_obj.strftime('%Y年%m月%d日%H时%M分%S秒')
                            
                    except ValueError as e:
                        logger.warning(f"无法解析时间格式: {date_time}, 错误: {e}")
                        date_time_obj = None
                        date_time_str = None

                # 交易流水号 col_transaction_no
                transaction_no = row[col_transaction_no] if col_transaction_no is not None and row[col_transaction_no] != '' else None

                # 摘要/用途
                summary = row[col_note] if col_note is not None and row[col_note] != '' else None

                # 交易类型
                transaction_type = row[col_purpose_code] if col_purpose_code is not None and row[col_purpose_code] != '' else None

                # 收支方向：从 借贷标志 列解码（样例里：1=借(支出), 2=贷(收入)）
                direction = None
                if col_direction is not None and row[col_direction] != '':
                    dr = row[col_direction].strip()
                    if dr in ('1', '借'):
                        direction = '借'
                    elif dr in ('2', '贷'):
                        direction = '贷'
                    else:
                        if '借' in dr:
                            direction = '借'
                        elif '贷' in dr:
                            direction = '贷'

                def to_float(x):
                    if x is None:
                        return None
                    s = str(x).replace(',', '').strip()
                    s = re.sub(r'[^\d\.\-]', '', s)
                    if s == '':
                        return None
                    try:
                        return float(s)
                    except:
                        return None
                # 金额 如果是支出，则取负值
                amount = to_float(row[col_amount]) if col_amount is not None else None
                if direction == '借':
                    amount = -amount
                balance = to_float(row[col_balance]) if col_balance is not None else None

                # 币种
                currency = row[col_currency] if col_currency is not None and row[col_currency] != '' else 'CNY'

                # 对方信息
                counterparty_name = row[col_counterparty_name] if col_counterparty_name is not None and row[col_counterparty_name] != '' else None
                counterparty_account = row[col_counterparty_account] if col_counterparty_account is not None and row[col_counterparty_account] != '' else None
                counterparty_bank = row[col_counterparty_bank] if col_counterparty_bank is not None and row[col_counterparty_bank] != '' else None
                counterparty_id = row[col_counterparty_id] if col_counterparty_id is not None and row[col_counterparty_id] != '' else None

                # 交易渠道、网点等
                channel = row[col_channel] if col_channel is not None and row[col_channel] != '' else None
                branch = row[col_branch] if col_branch is not None and row[col_branch] != '' else None
                remark = row[col_remark] if col_remark is not None and row[col_remark] != '' else None
                status = row[col_status] if col_status is not None and row[col_status] != '' else None

                # fallback: 最后一列可能包含对方信息
                if not counterparty_name:
                    last_val = row.iloc[-1]
                    if last_val:
                        counterparty_name = last_val
                transactions.append({
                    '交易时间': date_time_str,  # 格式化的字符串时间
                    '交易时间戳': date_time_obj,  # datetime对象
                    '交易流水号': transaction_no,
                    '交易账号': acc,
                    '户名': acc_name,
                    '摘要/用途': summary,
                    '交易类型': transaction_type,
                    '金额': amount,
                    '币种': currency,
                    '余额': balance,
                    '对方户名': counterparty_name,
                    '对方账号': counterparty_account,
                    '对方开户行': counterparty_bank,
                    '对方证件号': counterparty_id,
                    '交易渠道': channel,
                    '交易网点': branch,
                    '备注': remark,
                    '备注2': remark,
                    '交易状态': status
                })
                i += 1

        return transactions

    def parse(self, file_path):
        sheets = self._read_sheets_raw(file_path)

        all_accounts = []      # list of dicts
        all_transactions = []  # list of dicts

        for sheet_name, df in sheets.items():
            # 尝试 locate account header
            account_header_idxs = self._find_header_rows(df, ['查询ID', '客户编号', '姓名', '证件号码'])
            if account_header_idxs:
                # 取第一个作为账户信息起点
                accs = self._parse_account_block(df, account_header_idxs[0])
                # 若开户名缺失则使用 sheet 名作为回退
                for a in accs:
                    if not a.get('开户名'):
                        a['开户名'] = sheet_name
                all_accounts.extend(accs)
            else:
                # 没找到显式账户表头，后续从流水中尝试反推账号
                txs_from_sheet = self._parse_transaction_blocks(df, sheet_name)
                acc_nums = sorted({t['账号'] for t in txs_from_sheet if t.get('账号')})
                for accn in acc_nums:
                    all_accounts.append({
                        '账号': accn,
                        '开户名': sheet_name,
                        '开户行名称': self.BANK_NAME,
                        '证件号码': None
                    })
                all_transactions.extend(txs_from_sheet)

            # 不管是否找到账户头，都再解析流水段（避免重复解析时产生重复——下面去重或可按需改进）
            txs = self._parse_transaction_blocks(df, sheet_name)
            if txs:
                all_transactions.extend(txs)

        # 归一化 accounts：normalize_account_info 接受单条 dict（你当前的实现），这里逐条 normalize 并 concat
        account_dfs = []
        for a in all_accounts:
            try:
                account_dfs.append(normalize_account_info(a))
            except Exception as e:
                logger.exception("normalize_account_info 失败，record: %s  error: %s", a, e)
        account_df = pd.concat(account_dfs, ignore_index=True) if account_dfs else pd.DataFrame(columns=['账号','开户名','开户行名称','证件号码','其他字段1','其他字段2'])

        # 归一化 transactions：normalize_transaction_flow 接受 list
        try:
            transaction_df = normalize_transaction_flow(all_transactions) if all_transactions else pd.DataFrame(columns=['日期','摘要/用途','收支方向','金额','余额','对方账户信息','其他字段1','其他字段2'])
        except Exception as e:
            logger.exception("normalize_transaction_flow 失败: %s", e)
            transaction_df = pd.DataFrame(all_transactions)

        return account_df, transaction_df
